﻿using System;
using System.Data;
using Mono.Data.Sqlite;
using System.Text;

namespace Tab2Sql.Classes.db
{
    /// <summary>
    /// Class to interact with Sqlite DB
    /// </summary>
    public class internalDB
    {

        private SqliteDB sDB;
        private const string defaultFileName = "the.db";
        internal SqliteCommand myCommand;
        internal SqliteConnection myConn;
        /// <summary>
        ///
        /// </summary>
        public StringBuilder Output;
        /// <summary>
        ///
        /// </summary>
        public DataTable Schema
        {
            get
            {
                return sDB.Schema;
            }
        }
        /// <summary>
        ///
        /// </summary>
        public string BasicInfo
        {
            get
            {
                return sDB.BasicInfo + "\n" + sDB.FileName;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public string SchemaInfo
        {
            get
            {

                return sDB.DisplaySchema(false);
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="rawText">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="convert2sql">
        /// A <see cref="System.Boolean"/>
        /// </param>
        public internalDB(string rawText, bool convert2sql)
        {
            initDB();

            var ts2sql = new TSV2SQL(rawText, SqlVariants.Sqlite);
            string sqlStatements = ts2sql.ToString();
            sqlStatements = sqlStatements.Replace("CREATE TEMP ", "CREATE ");
            string likelyTableName = ts2sql.TableName.Replace("#", String.Empty);
            sqlStatements = sqlStatements.Replace(likelyTableName, getTableName());
            myCommand.CommandText = sqlStatements + Environment.NewLine + "COMMIT;";

            var dt = new DataTable();
            myConn.Open();
            myConn.BeginTransaction();
            IDataReader ir = myCommand.ExecuteReader();
            dt.Load(ir);
            myConn.Close();
            Output.AppendLine(Environment.CurrentDirectory);
            Output.AppendLine(myConn.ConnectionString);
            Output.AppendLine(DGen.DText.ObjectToString.DataTableToString(dt));
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="fileNameSupplied">
        /// A <see cref="System.Boolean"/>
        /// </param>
        /// <param name="dbFileName">
        /// A <see cref="System.String"/>
        /// </param>

        public internalDB(bool fileNameSupplied, string dbFileName)
        {
            initDB(dbFileName);
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sqlStatement">
        /// A <see cref="System.String"/>
        /// </param>
        public internalDB(string sqlStatement)
        {
            initDB();
            myCommand.CommandText = sqlStatement;
            var dt = new DataTable();
            IDataReader ir = myCommand.ExecuteReader();
            dt.Load(ir);
            Output.AppendLine(DGen.DText.ObjectToString.DataTableToString(dt));
        }
        /// <summary>
        ///
        /// </summary>
        public internalDB()
        {

            initDB();
        }

        /// <summary>
        /// generates a non random but relatively unique table name
        /// </summary>
        /// <returns></returns>
        private string getTableName()
        {
            string td = DateTime.Now.ToShortDateString() + DateTime.Now.ToLongTimeString();
            td = td.Replace(":", String.Empty);
            td = td.Replace("-", String.Empty);
            td = td.Replace(" ", String.Empty);
            td = td.Replace("/", String.Empty);
            td = td.Replace(".", String.Empty);
            return "t" + td;
        }


        private void initDB()
        {
            sDB = new SqliteDB(defaultFileName);
            Output = new StringBuilder();
            
        }
        private void initDB(string fileName)
        {
            sDB = new SqliteDB(fileName);
            Output = new StringBuilder();

        }



        private void testStuff()
        {
            var conn = new SqliteConnection();
            var cmd = new SqliteCommand(conn);
            dropIfExists("myTable", conn);
            if (exists("mytable", conn) == false)
            {
                cmd.CommandText = "CREATE TABLE mytable (ID INT, Test TEXT)";
                Output.AppendLine(cmd.ExecuteNonQuery() + " rows");
            }


            cmd.CommandText = "INSERT INTO myTable VALUES (1, 'Hello World');";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SELECT * FROM myTable";
            var dt = new DataTable();
            IDataReader ir = cmd.ExecuteReader();
            dt.Load(ir);
            Output.AppendLine(DGen.DText.ObjectToString.DataTableToString(dt));
        }


        private void dropIfExists(string p, SqliteConnection conn)
        {
            var myCmd = new SqliteCommand(conn);
            if (exists(p, conn))
            {
                myCmd.CommandText = "DROP TABLE " + p;
                myCmd.ExecuteNonQuery();
            }
        }


        private bool exists(string Table, SqliteConnection theConn)
        {
            DataTable myT = theConn.GetSchema(SqliteMetaDataCollectionNames.Tables);
            foreach (DataRow dr in myT.Rows)
            {
                string tabName = dr["TABLE_NAME"].ToString();
                if (tabName.ToLower() == Table.ToLower())
                {
                    return true;
                }
            }
            return false;
        }




        internal void Process(string p)
        {
            int i = 0;
            string msg = "";
            try
            {
                i = sDB.ExecuteSql(p);
                msg = string.Format("{0} rows affected", i);

            }
            catch(Exception ex)
            {
                msg = "Error."+ex.Message;
            }
            Output.AppendLine(msg);
        }
    }
}